package com.turing.post.capitalaccounts.impl;

import java.math.BigInteger;
import java.util.Date;
import java.util.List;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.capitalaccounts.util.BankAccountDetail;
import com.turing.post.capitalaccounts.util.BankAccountDetailDao;
import com.turing.post.common.util.Global;
import com.turing.post.common.util.PCPSCommonDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * 银行帐户入账流水Dao实现
 * 
 * @author liun
 * @date 2013-05-29
 */
@Stateless(mappedName = "capitalaccounts.impl.BankAccountDetailDaoImpl")
@Remote
@Local
public class BankAccountDetailDaoImpl implements BankAccountDetailDao {

	/**
	 * 实体管理器
	 */
	@PersistenceContext
	protected EntityManager _em;

	@EJB(mappedName = "common.impl.PCPSCommonDaoImpl")
	private PCPSCommonDao pcpsCommonDaoImpl;

	/**
	 * 根据银行账户ID获取银行账户入账流水
	 * 
	 * @param bankAccountId
	 *            银行账户ID
	 * @param page
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<BankAccountDetail> getBankAccountDetailByAccID(
			PCPSWebInput webInput, BigInteger accountNoId, String status,
			Date startDate, Date endDate, String voucherNo,
			BigInteger startAmount, BigInteger endAmount,
			BigInteger createBranchNo, String isSingle, String paidType, String lenghtSection,
			Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "SELECT B.* FROM BANKACCOUNTDETAIL B "
				+ "WHERE B.CREATETIME >= TO_DATE(?1, 'yyyy-MM-dd hh24:mi:ss') "
				+ "AND B.CREATETIME <= TO_DATE(?2, 'yyyy-MM-dd hh24:mi:ss') "
				+ "AND B.VOUCHERAMOUNT >= ?3 AND B.VOUCHERAMOUNT <= ?4 ";
		if (!PCPSUtil.isEmpty(paidType)) {
			sql = sql + "AND B.PAIDTYPE = '" + paidType + "' ";
		}
		// 如果没有输入银行帐号，取当前登录帐号所属发卡上的所有帐号流水；若输入了帐号，取帐号流水
		if (accountNoId != null) {
			if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
				sql = sql + "AND B.BANKACCOUNTID = " + accountNoId.toString()
						+ " ";
			}
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + "AND B.STATUS = '" + status + "' ";
		}
		if (!PCPSUtil.isEmpty(voucherNo)) {
			sql = sql + "AND B.VOUCHERNO = '" + voucherNo + "' ";
		}
		if (createBranchNo != null) {
			if (!PCPSUtil.isEmpty(isSingle)) {
				if (isSingle.equals("1")) {
					sql = sql
							+ "AND (EXISTS (SELECT * FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
							+ createBranchNo.toString()
							+ " AND S.SUBBRANCHNO = B.CREATEBRANCHNO) OR B.CREATEBRANCHNO = "
							+ createBranchNo.toString() + ") ";
				} else {
					sql = sql + "AND B.CREATEBRANCHNO = "
							+ createBranchNo.toString() + " ";
				}
			}
		} else {
			if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
				sql = sql
						+ "AND (EXISTS (SELECT * FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
						+ webInput.getOperBranchNo()
						+ " AND S.SUBBRANCHNO = B.CREATEBRANCHNO) OR B.CREATEBRANCHNO = "
						+ webInput.getOperBranchNo() + ") ";
			}
		}
		if(lenghtSection.equals(BankAccountDetail.LENGHTSECTION_LENGTH)){
			sql = sql + "AND B.VOUCHERAMOUNT > B.DEBITAMOUNT AND B.PaidType = '1' ";
		}
		sql = sql + " AND B.STATUS <>'" + BankAccountDetail.Status_Input
				+ "' ORDER BY B.CreateBranchNo,B.CREATETIME DESC";
		if (startDate == null) {
			startDate = new Date();
		}
		if (endDate == null) {
			endDate = new Date();
		}
		if (startAmount == null) {
			startAmount = Config.startAmount;
		}
		if (endAmount == null) {
			endAmount = Config.endAmount;
		}
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, PCPSUtil.getPackedYMDString2(startDate)
				+ " 00:00:00");
		query.setParameter(2, PCPSUtil.getPackedYMDString2(endDate)
				+ " 23:59:59");
		query.setParameter(3, startAmount);
		query.setParameter(4, endAmount);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BankAccountDetail> getBankAccountDetailListBySaleBranchNo(
			PCPSWebInput webInput) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		Global global = pcpsCommonDaoImpl.getGlobalInfo();
		Integer size = global.getBankAccountDetailSize() + 1;
		String sql = "select * from (select t.* from BankAccountDetail t where t.createbranchno='"
				+ webInput.getOperBranchNo()
				+ "' and PaidBankAccountId is null and STATUS<>'"
				+ BankAccountDetail.Status_Cancel
				+ "' and STATUS<>'"
				+ BankAccountDetail.Status_Input
				+ "' order by t.createtime desc) a where rownum<" + size;
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<BankAccountDetail> getBankAccountDetailList(
			PCPSWebInput webInput, String saleBranchNo, int page) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sql = "select * from bankaccountdetail t where t.createbranchno='"
				+ saleBranchNo
				+ "' "
				+ " and t.status in('"
				+ BankAccountDetail.STATUS_AUTOCHECK
				+ "','"
				+ BankAccountDetail.STATUS_MANUALLYCHECK
				+ "','"
				+ BankAccountDetail.STATUS_NOCHECK
				+ "','"
				+ BankAccountDetail.Status_Input
				+ "') and t.voucheramount>t.debitamount " 
				+ " and PaidType = 1 order  by t.createtime desc";
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public Integer getBankAccountDetailTotal(PCPSWebInput webInput,
			String saleBranchNo) {
		if (webInput == null || PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
			return null;
		}
		String sql = "select nvl(count(*),0) from bankaccountdetail t where t.createbranchno='"
				+ saleBranchNo
				+ "' "
				+ " and t.status in('"
				+ BankAccountDetail.STATUS_AUTOCHECK
				+ "','"
				+ BankAccountDetail.STATUS_MANUALLYCHECK
				+ "','"
				+ BankAccountDetail.STATUS_NOCHECK
				+ "','"
				+ BankAccountDetail.Status_Input
				+ "') and t.voucheramount>t.debitamount "
				+" and PaidType = 1 order by t.createtime desc";
		Query query = _em.createNativeQuery(sql);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * 根据id获取银行账户入账流水
	 * 
	 * @param bankAccDetailId
	 *            银行账户入账流水ID
	 * @return
	 */
	@Override
	public BankAccountDetail getBankAccountDetailByID(BigInteger bankAccDetailId) {
		return _em.find(BankAccountDetail.class, bankAccDetailId);
	}

	/**
	 * 获取银行账户入账流水总数
	 * 
	 * @param bankAccountId
	 *            银行账户ID
	 * @return
	 */
	@Override
	public Integer getBankAccountDetailByAccIDTotal(PCPSWebInput webInput,
			BigInteger accountNoId, String status, Date startDate,
			Date endDate, String voucherNo, BigInteger startAmount,
			BigInteger endAmount, BigInteger createBranchNo, String isSingle,
			String paidType, String lenghtSection) {
		String sql = "SELECT NVL(COUNT(*),0) FROM BANKACCOUNTDETAIL B "
				+ "WHERE B.CREATETIME >= TO_DATE(?1, 'yyyy-MM-dd hh24:mi:ss') "
				+ "AND B.CREATETIME <= TO_DATE(?2, 'yyyy-MM-dd hh24:mi:ss') "
				+ "AND B.VOUCHERAMOUNT >= ?3 AND B.VOUCHERAMOUNT <= ?4 ";
		if (!PCPSUtil.isEmpty(paidType)) {
			sql = sql + "AND B.PAIDTYPE = '" + paidType + "' ";
		}
		// 如果没有输入银行帐号，取当前登录帐号所属发卡上的所有帐号流水；若输入了帐号，取帐号流水
		if (accountNoId != null) {
			if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
				sql = sql + "AND B.BANKACCOUNTID = " + accountNoId.toString()
						+ " ";
			}
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + "AND B.STATUS = '" + status + "' ";
		}
		if (!PCPSUtil.isEmpty(voucherNo)) {
			sql = sql + "AND B.VOUCHERNO = '" + voucherNo + "' ";
		}
		if (createBranchNo != null) {
			if (!PCPSUtil.isEmpty(isSingle)) {
				if (isSingle.equals("1")) {
					sql = sql
							+ "AND EXISTS (SELECT * FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
							+ createBranchNo.toString()
							+ " AND S.SUBBRANCHNO = B.CREATEBRANCHNO) ";
				} else {
					sql = sql + "AND B.CREATEBRANCHNO = "
							+ createBranchNo.toString() + " ";
				}
			}
		} else {
			if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
				sql = sql
						+ "AND (EXISTS (SELECT * FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
						+ webInput.getOperBranchNo()
						+ " AND S.SUBBRANCHNO = B.CREATEBRANCHNO) OR B.CREATEBRANCHNO = "
						+ webInput.getOperBranchNo() + ") ";
			}
		}
		if(lenghtSection.equals(BankAccountDetail.LENGHTSECTION_LENGTH)){
			sql = sql + "AND B.VOUCHERAMOUNT > B.DEBITAMOUNT AND B.PaidType = '1' ";
		}
		if (startDate == null) {
			startDate = new Date();
		}
		if (endDate == null) {
			endDate = new Date();
		}
		if (startAmount == null) {
			startAmount = Config.startAmount;
		}
		if (endAmount == null) {
			endAmount = Config.endAmount;
		}
		sql += " AND B.STATUS <>'" + BankAccountDetail.Status_Input + "'";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, PCPSUtil.getPackedYMDString2(startDate)
				+ " 00:00:00");
		query.setParameter(2, PCPSUtil.getPackedYMDString2(endDate)
				+ " 23:59:59");
		query.setParameter(3, startAmount);
		query.setParameter(4, endAmount);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * 添加一条银行账户入账流水明细
	 * 
	 * @param entity
	 */
	@Override
	public void addBankAccountDetail(BankAccountDetail entity)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, entity);
	}

	/**
	 * 根据ID删除一条银行账户入账流水明细
	 * 
	 * @param entity
	 */
	@Override
	public void delBankAccountDetail(BankAccountDetail entity)
			throws PCPSApplicationException {
		GeneralDao.remove(_em, entity);
	}

	/**
	 * 锁定一条银行账户入账流水明细
	 * 
	 * @return
	 */
	@Override
	public BankAccountDetail getBankAccountDetailForUpdate(
			BigInteger bankAccDetailId) {
		String sql = "SELECT B.* " + "FROM BANKACCOUNTDETAIL B "
				+ "WHERE B.BANKACCDETAILID = ?1 FOR UPDATE";
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class);
		query.setParameter(1, bankAccDetailId);
		return (BankAccountDetail) query.getSingleResult();
	}

	/**
	 * 修改一条银行账户入账流水明细
	 * 
	 * @param entity
	 * @throws PCPSApplicationException
	 */
	@Override
	public void modifyBankAccountDetail(BankAccountDetail entity)
			throws PCPSApplicationException {
		GeneralDao.update(_em, entity);
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public BankAccountDetail getBankAccountDetailByVoucher(String voucherNo)
	// {
	// String sql = "SELECT * FROM BANKACCOUNTDETAIL B "
	// + "WHERE voucherNo=?1";
	// Query query = _em.createNativeQuery(sql, BankAccountDetail.class);
	// query.setParameter(1, voucherNo);
	// List<BankAccountDetail> list = query.getResultList();
	// if (list == null || list.size() < 0) {
	// return null;
	// }
	// return list.get(0);
	// }

	@SuppressWarnings("unchecked")
	@Override
	public BankAccountDetail getBankAccountDetailByVoucherAndAmount(
			BigInteger bankAccountId, String voucherNo, BigInteger voucherAmount) {
		String sql = "SELECT * FROM BANKACCOUNTDETAIL B "
				+ " WHERE B.BankAccountId = ?1 AND B.VoucherNo = ?2 AND VoucherAmount=?3 ";
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class);
		query.setParameter(1, bankAccountId);
		query.setParameter(2, voucherNo);
		query.setParameter(3, voucherAmount);
		List<BankAccountDetail> list = query.getResultList();
		if (list == null || list.size() <= 0) {
			return null;
		}
		return list.get(0);
	}

	@SuppressWarnings("unchecked")
	@Override
	public BankAccountDetail getBankAccountDetailByUNI(String voucherNo,
			BigInteger paidBankAccountId, String status) {
		String sql = "SELECT * FROM BANKACCOUNTDETAIL B "
				+ " WHERE B.VOUCHERNO = ?1 AND B.PAIDBANKACCOUNTID = ?2 AND B.STATUS = ?3 ";
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class);
		query.setParameter(1, voucherNo);
		query.setParameter(2, paidBankAccountId);
		query.setParameter(3, status);
		List<BankAccountDetail> list = query.getResultList();
		if (list == null || list.size() <= 0) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public BigInteger getBankAccountDetailByTotalAmount(PCPSWebInput webInput,
			BigInteger accountNoId, String status, Date startDate,
			Date endDate, String voucherNo, BigInteger startAmount,
			BigInteger endAmount, BigInteger createBranchNo, String isSingle) {

		String sql = "SELECT NVL(SUM(VOUCHERAMOUNT),0) FROM BANKACCOUNTDETAIL B "
				+ "WHERE B.CREATETIME >= TO_DATE(?1, 'yyyy-MM-dd hh24:mi:ss') "
				+ "AND B.CREATETIME <= TO_DATE(?2, 'yyyy-MM-dd hh24:mi:ss') "
				+ "AND B.VOUCHERAMOUNT >= ?3 AND B.VOUCHERAMOUNT <= ?4 ";
		// 如果没有输入银行帐号，取当前登录帐号所属发卡上的所有帐号流水；若输入了帐号，取帐号流水
		if (accountNoId != null) {
			if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
				sql = sql + "AND B.BANKACCOUNTID = " + accountNoId.toString()
						+ " ";
			}
		}
		if (!PCPSUtil.isEmpty(status)) {
			sql = sql + "AND B.STATUS = '" + status + "' ";
		}
		if (!PCPSUtil.isEmpty(voucherNo)) {
			sql = sql + "AND B.VOUCHERNO = '" + voucherNo + "' ";
		}
		if (createBranchNo != null) {
			if (!PCPSUtil.isEmpty(isSingle)) {
				if (isSingle.equals("1")) {
					sql = sql
							+ "AND EXISTS (SELECT * FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
							+ createBranchNo.toString()
							+ " AND S.SUBBRANCHNO = B.CREATEBRANCHNO) ";
				} else {
					sql = sql + "AND B.CREATEBRANCHNO = "
							+ createBranchNo.toString() + " ";
				}
			}
		} else {
			if (!PCPSUtil.isEmpty(webInput.getOperBranchNo())) {
				sql = sql
						+ "AND (EXISTS (SELECT * FROM SYSBRANCHSUBORD S WHERE S.BRANCHNO = "
						+ webInput.getOperBranchNo()
						+ " AND S.SUBBRANCHNO = B.CREATEBRANCHNO) OR B.CREATEBRANCHNO = "
						+ webInput.getOperBranchNo() + ") ";
			}
		}
		sql = sql + " AND B.STATUS <>'" + BankAccountDetail.Status_Input
				+ "' ORDER BY B.CreateBranchNo,B.CREATETIME DESC";
		if (startDate == null) {
			startDate = new Date();
		}
		if (endDate == null) {
			endDate = new Date();
		}
		if (startAmount == null) {
			startAmount = Config.startAmount;
		}
		if (endAmount == null) {
			endAmount = Config.endAmount;
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, PCPSUtil.getPackedYMDString2(startDate)
				+ " 00:00:00");
		query.setParameter(2, PCPSUtil.getPackedYMDString2(endDate)
				+ " 23:59:59");
		query.setParameter(3, startAmount);
		query.setParameter(4, endAmount);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public BigInteger getVoucherAmountTotalByIssueBranchNo(
			String issueBranchNo, String tranDate) {
		String sql = "SELECT NVL(SUM(A.VOUCHERAMOUNT), 0) "
				+ "FROM BANKACCOUNTDETAIL A "
				+ "WHERE A.BANKACCOUNTID IN (SELECT  B.BANKACCOUNTID FROM BANKACCOUNT B WHERE B.BRANCHNO = ?) "
				+ "AND A.AUTHTIME IS NOT NULL "
				+ "AND TO_DATE(TO_CHAR(A.AUTHTIME, 'YYYY-MM-DD'), 'YYYY-MM-DD') = TO_DATE('"
				+ tranDate + "', 'YYYY-MM-DD') "
				+ "AND (A.STATUS = '0' OR A.STATUS = '1')";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueBranchNo);
		return new BigInteger(query.getSingleResult().toString());
	}

	@Override
	public Integer checkBankAccountDetailVoucherNo(String issueCompanyNo,
			String voucherNo) {
		String sql = "select nvl(count(*),0) from BankAccountDetail t"
				+ " where t.CreateBranchNo like ?1 and t.voucherNo = ?2"
				+ " and t.status <> '3'";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issueCompanyNo + "%");
		query.setParameter(2, voucherNo);
		return new Integer(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public BankAccountDetail getBankAccountDetailByVoucherAndBankAccSeqNo(
			BigInteger bankAccountId, String voucherNo, String bankAccSeqNo) {
		String sql = "SELECT * FROM BANKACCOUNTDETAIL B "
				+ " WHERE B.BankAccountId = ?1 AND B.VoucherNo = ?2 AND BankAccSeqNo = ?3 ";
		Query query = _em.createNativeQuery(sql, BankAccountDetail.class);
		query.setParameter(1, bankAccountId);
		query.setParameter(2, voucherNo);
		query.setParameter(3, bankAccSeqNo);
		List<BankAccountDetail> list = query.getResultList();
		if (list == null || list.size() <= 0) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public Integer getBankAccountDetailTotalByCond(PCPSWebInput webInput,
			String branchNo, String status) {
		String sql = "select nvl(count(*),0) from bankaccountdetail t where t.status=?1";
		sql += PCPSUtil.getSqlCond(webInput, branchNo, "t.createbranchno");
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, status);
		return Integer.parseInt(query.getSingleResult().toString());
	}
}
